import datetime
from datetime import datetime
from conn import connect_to_db


class Database:
    """数据库连接与通用操作类"""
    def __init__(self):
        self.conn = None
        self.cur = None

    def __enter__(self):
        """上下文管理器初始化"""
        self.conn = connect_to_db()
        self.cur = self.conn.cursor()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """自动资源清理"""
        self.close()

    def execute(self, sql, params=None):
        """执行SQL查询"""
        try:
            self.cur.execute(sql, params)
            return self.cur.fetchall()
        except Exception as e:
            raise RuntimeError(f"数据库执行错误: {str(e)}")

    def close(self):
        """安全关闭数据库连接"""
        if hasattr(self, 'cur') and self.cur:
            self.cur.close()
        if hasattr(self, 'conn') and self.conn:
            self.conn.close()


class GarbageDataHandler:
    """垃圾数据处理类"""
    SQL_CLEANED_GARBAGE = """
        SELECT 
            r.community_name AS communityName,
            r.address AS address,
            c.street_name AS streetName,
            r.x AS x,
            r.y AS y,
            SUM(CASE WHEN v.type_name = '干垃圾' THEN v.garbage_weight ELSE 0 END) AS dryWeight,
            SUM(CASE WHEN v.type_name = '湿垃圾' THEN v.garbage_weight ELSE 0 END) AS wetWeight,
            SUM(CASE WHEN v.type_name = '餐厨垃圾' THEN v.garbage_weight ELSE 0 END) AS restaurantWeight,
            SUM(CASE WHEN v.type_name = '厨余垃圾' THEN v.garbage_weight ELSE 0 END) AS foodWeight,
            ARRAY_AGG(DISTINCT v.vehicle_license_num) AS vehicles
        FROM tb_reside_area_base_info r
        JOIN tb_community_name_correlation c ON r.community_name = c.base_community_name
        JOIN tb_vehicle_weighing v ON c.vehicle_community_name = v.community_name
        WHERE v.load_time_str BETWEEN %s AND %s
            AND v.type_name IN ('干垃圾', '湿垃圾', '餐厨垃圾', '厨余垃圾')
        GROUP BY r.community_name, r.address, c.street_name, r.x, r.y
        HAVING
            SUM(CASE WHEN v.type_name = '干垃圾' THEN 1 ELSE 0 END) > 0
            AND SUM(CASE WHEN v.type_name IN ('湿垃圾', '餐厨垃圾', '厨余垃圾') THEN 1 ELSE 0 END) > 0
        ORDER BY r.community_name;
    """

    SQL_COMMUNITY_STREET = """
        SELECT 
            t1.street AS 街道名称,
            t1.community_name AS 小区名称,
            t1.x AS 小区x坐标,
            t1.y AS 小区y坐标,
            t2.converted_baidu_longitude AS 清运点经度,
            t2.converted_baidu_latitude AS 清运点纬度,
            SUM(CASE WHEN t2.type_name = '干垃圾' THEN t2.garbage_weight ELSE 0 END) AS 干垃圾量,
            SUM(CASE WHEN t2.type_name = '厨余垃圾' THEN t2.garbage_weight ELSE 0 END) AS 厨余垃圾量,
            SUM(CASE WHEN t2.type_name = '餐厨垃圾' THEN t2.garbage_weight ELSE 0 END) AS 餐厨垃圾量,
            SUM(CASE WHEN t2.type_name = '湿垃圾' THEN t2.garbage_weight ELSE 0 END) AS 湿垃圾量
        FROM tb_reside_area_base_info t1
        INNER JOIN tb_community_name_correlation t3 
            ON t1.community_name = t3.base_community_name
        INNER JOIN tb_vehicle_weighing t2 
            ON t3.vehicle_community_name = t2.community_name
        WHERE {conditions}
        GROUP BY 
            t1.street,
            t1.community_name,
            t1.x,
            t1.y,
            t2.converted_baidu_longitude,
            t2.converted_baidu_latitude;
    """

    SQL_OLD_RUBBISH = """
    SELECT 
        t1.street AS 街道名称,
        t1.community_name AS 小区名称,
        t2.bg_order_id AS 预约单id,
        t2.is_resident_desc AS 小区类型,
        t1.address AS 小区地址,
        t1.x AS 小区x坐标,
        t1.y AS 小区y坐标,
        t2.garbage_put_addr AS 堆放点,
        t2.garbage_type_name AS 垃圾类型,
        t2.garbage_weight AS 预约量袋,
        t2.create_time_str AS 上报时间,
        t2.estimate_clear_time_str AS 预约清运时间,
        t2.order_over_time_str AS 超时时间,
        t2.deal_order_time_str AS 接单时间,
        t2.finish_time_str AS 完成时间,
        t2.order_state AS 状态,
        t2.order_mode_desc AS 状态描述,
        t2.upgrade_mode_str AS 推进模式,
        CASE WHEN t2.order_state = 5 THEN '是' ELSE '否' END AS 是否逾期
    FROM 
        tb_reside_area_base_info t1
    INNER JOIN 
        tb_community_name_correlation t3 
        ON t1.community_name = t3.base_community_name
    INNER JOIN 
        tb_decoration_waste_reservation t2 
        ON t3.vehicle_community_name = t2.community_name
    WHERE {conditions}
    """

    SQL_NEW_RUBBISH = """
    SELECT 
        t1.street AS 街道名称,
        t1.community_name AS 小区名称,
        t1.x AS 小区x坐标,
        t1.y AS 小区y坐标,
        t2.converted_baidu_longitude AS 清运点经度,
        t2.converted_baidu_latitude AS 清运点纬度,
        SUM(CASE WHEN t2.type_name = '干垃圾' THEN t2.garbage_weight ELSE 0 END) AS 干垃圾量,
        SUM(CASE WHEN t2.type_name = '厨余垃圾' THEN t2.garbage_weight ELSE 0 END) AS 厨余垃圾量,
        SUM(CASE WHEN t2.type_name = '餐厨垃圾' THEN t2.garbage_weight ELSE 0 END) AS 餐厨垃圾量,
        SUM(CASE WHEN t2.type_name = '湿垃圾' THEN t2.garbage_weight ELSE 0 END) AS 湿垃圾量
    FROM 
        tb_reside_area_base_info t1
    INNER JOIN 
        tb_community_name_correlation t3 
        ON t1.community_name = t3.base_community_name
    INNER JOIN 
        tb_vehicle_weighing t2 
        ON t3.vehicle_community_name = t2.community_name
    WHERE {conditions}
    GROUP BY 
        t1.street,
        t1.community_name,
        t1.x,
        t1.y,
        t2.converted_baidu_longitude,
        t2.converted_baidu_latitude;
    """
    SQL_FULL_RUBBISH = """
       SELECT 
           t1.street AS street_name,
           t1.community_name AS community_name,
           t1.x AS community_x,
           t1.y AS community_y,
           t2.event_id AS event_id,
           t2.station_id AS station_id,
           t2.station_name AS station_name,
           t3.longitude AS station_longitude,
           t3.latitude AS station_latitude,
           t2.division_name AS division_name,
           t2.full_time AS full_time,
           t2.camera_image_urls AS full_images,
           t2.handle_time AS handle_time,
           t2.is_handle AS is_handled,
           t2.handle_image_urls AS handle_images,
           t2.processed AS is_processed,
           t2.process_time AS process_time
       FROM 
           tb_reside_area_base_info t1
       INNER JOIN 
           tb_garbage_full_event_data t2 
           ON t2.community_name = t1.community_name
       INNER JOIN 
           tb_garbage_stations_info t3 
           ON t3.id = t2.station_id
       WHERE {conditions};
       """
    SQL_SMALL_RUBBISH = """
    SELECT 
    t1.street AS street_name,
    t1.community_name AS community_name,
    t2.drop_time AS drop_time,
    t1.x AS community_x,
    t1.y AS community_y,
    t2.event_id AS event_id,
    t2.station_id AS station_id,
    t2.station_name AS station_name,
    t3.longitude AS station_longitude,
    t3.latitude AS station_latitude,
    t2.division_name AS division_name,
    t2.drop_time AS drop_time,  
    t2.drop_image_urls AS drop_images,
    t2.handle_time AS handle_time,
    t2.is_handle AS is_handled,
    t2.handle_image_urls AS handle_images,
    t2.is_timeout AS is_timeout,
    t2.timeout_image_urls AS timeout_images,
    t2.record_no AS record_no,
    t2.take_minutes AS take_minutes
FROM 
    tb_reside_area_base_info t1
INNER JOIN 
    tb_garbage_drop_event_data t2 
    ON t2.community_name = t1.community_name
INNER JOIN 
    tb_garbage_stations_info t3 
    ON t3.id = t2.station_id
WHERE {conditions};
"""

    SQL_INSPECTION_RECTIFICATION = """
      SELECT  
          r.street AS street_name,
          r.community_name AS community_name,
          r.x AS community_x,
          r.y AS community_y,
          stats.inspection_count,
          stats.issue_count,
          stats.rectified_count,
          stats.pending_rectification_count
      FROM    
          tb_reside_area_base_info r
      INNER JOIN (
          SELECT 
              area.street,
              area.community_name,
              COUNT(xc.id) AS inspection_count,
              SUM(CASE WHEN xc.total > 0 THEN 1 ELSE 0 END) AS issue_count,
              SUM(CASE WHEN xc.total > 0 AND EXISTS (
                  SELECT 1 FROM tb_xc_zg WHERE fromId = xc.id
                  ) THEN 1 ELSE 0 END) AS rectified_count,
              SUM(CASE WHEN total > 0 AND NOT EXISTS (
                  SELECT 1 FROM tb_xc_zg WHERE fromId = xc.id
                  ) THEN 1 ELSE 0 END) AS pending_rectification_count
          FROM 
              tb_reside_area_base_info area
          INNER JOIN 
              tb_xc_community_name_correlation corr 
              ON area.community_name = corr.base_community_name
          INNER JOIN         
              tb_xc xc 
              ON corr.xc_community_name = xc.name
          WHERE 
              {conditions}
          GROUP BY 
              area.community_name, area.street 
      ) stats 
      ON r.community_name = stats.community_name;
      """

    def get_cleaned_garbage_data(self, start_time, end_time):
        """获取清洁垃圾详情数据"""
        query_start = f"{start_time} 00:00:00"
        query_end = f"{end_time} 23:59:59"

        with Database() as db:
            rows = db.execute(self.SQL_CLEANED_GARBAGE, (query_start, query_end))

        return [{
            "communityName": row[0],
            "address": row[1],
            "streetName": row[2],
            "x": float(row[3]) if row[3] is not None else 0.0,
            "y": float(row[4]) if row[4] is not None else 0.0,
            "dryWeight": round(float(row[5]), 2) if row[5] else 0.0,
            "wetWeight": round(float(row[6]), 2) if row[6] else 0.0,
            "restaurantWeight": round(float(row[7]), 2) if row[7] else 0.0,
            "foodWeight": round(float(row[8]), 2) if row[8] else 0.0,
            "vehicles": row[9] if row[9] else []
        } for row in rows]

    def get_community_street_data(self, conditions, params):
        """获取社区街道数据"""
        sql = self.SQL_COMMUNITY_STREET.format(conditions=" AND ".join(conditions))

        with Database() as db:
            rows = db.execute(sql, params)

        return [{
            "街道名称": row[0],
            "小区名称": row[1],
            "小区x坐标": float(row[2]) if row[2] is not None else 0.0,
            "小区y坐标": float(row[3]) if row[3] is not None else 0.0,
            "清运点经度": float(row[4]) if row[4] is not None else 0.0,
            "清运点纬度": float(row[5]) if row[5] is not None else 0.0,
            "干垃圾量": round(float(row[6]), 2) if row[6] else 0.0,
            "厨余垃圾量": round(float(row[7]), 2) if row[7] else 0.0,
            "餐厨垃圾量": round(float(row[8]), 2) if row[8] else 0.0,
            "湿垃圾量": round(float(row[9]), 2) if row[9] else 0.0
        } for row in rows]

    def get_old_rubbish_data(self, conditions, params):
        """获取预约垃圾数据"""
        sql = self.SQL_OLD_RUBBISH.format(conditions=" AND ".join(conditions))

        with Database() as db:
            rows = db.execute(sql, params)

        return [{
            "街道名称": row[0],
            "小区名称": row[1],
            "预约单id": row[2],
            "小区类型": row[3],
            "小区地址": row[4],
            "小区x坐标": float(row[5]) if row[5] is not None else 0.0,
            "小区y坐标": float(row[6]) if row[6] is not None else 0.0,
            "堆放点": row[7],
            "垃圾类型": row[8],
            "预约量袋": float(row[9]) if row[9] is not None else 0.0,
            "上报时间": str(row[10]) if row[10] else None,
            "预约清运时间": str(row[11]) if row[11] else None,
            "超时时间": str(row[12]) if row[12] else None,
            "接单时间": str(row[13]) if row[13] else None,
            "完成时间": str(row[14]) if row[14] else None,
            "状态": row[15],
            "状态描述": row[16],
            "推进模式": row[17],
            "是否逾期": row[18]
        } for row in rows]

    def get_new_rubbish_data(self, conditions, params):
        """获取新型垃圾数据"""
        sql = self.SQL_NEW_RUBBISH.format(conditions=" AND ".join(conditions))

        with Database() as db:
            rows = db.execute(sql, params)

        return [{
            "街道名称": row[0],
            "小区名称": row[1],
            "小区x坐标": float(row[2]) if row[2] is not None else 0.0,
            "小区y坐标": float(row[3]) if row[3] is not None else 0.0,
            "清运点经度": float(row[4]) if row[4] is not None else 0.0,
            "清运点纬度": float(row[5]) if row[5] is not None else 0.0,
            "干垃圾量": round(float(row[6]), 2) if row[6] else 0.0,
            "厨余垃圾量": round(float(row[7]), 2) if row[7] else 0.0,
            "餐厨垃圾量": round(float(row[8]), 2) if row[8] else 0.0,
            "湿垃圾量": round(float(row[9]), 2) if row[9] else 0.0
        } for row in rows]

    def get_full_rubbish_data(self, conditions, params):
        """获取垃圾满溢事件数据"""
        sql = self.SQL_FULL_RUBBISH.format(conditions=" AND ".join(conditions))

        with Database() as db:
            rows = db.execute(sql, params)

        return [{
            "街道名称": row[0],
            "小区名称": row[1],
            "小区坐标": {"x": float(row[2]) if row[2] is not None else 0.0,
                         "y": float(row[3]) if row[3] is not None else 0.0},
            "事件ID": row[4],
            "垃圾房信息": {
                "ID": row[5],
                "名称": row[6],
                "坐标": {
                    "经度": float(row[7]) if row[7] is not None else 0.0,
                    "纬度": float(row[8]) if row[8] is not None else 0.0
                }
            },
            "区划名称": row[9],
            "满溢时间": str(row[10]) if row[10] else None,
            "满溢图片": row[11].split(',') if row[11] else [],
            "处置信息": {
                "处置时间": str(row[12]) if row[12] else None,
                "是否处置": bool(row[13]),
                "处置图片": row[14].split(',') if row[14] else [],
                "处置人员是否处置": bool(row[15]),
                "处置人员处置时间": str(row[16]) if row[16] else None
            }
        } for row in rows]


    def get_small_rubbish_data(self, conditions, params):
        """获取小包垃圾事件数据"""
        sql = self.SQL_SMALL_RUBBISH.format(conditions=" AND ".join(conditions))
        with Database() as db:
            rows = db.execute(sql, params)
        return[{
            "街道名称": row[0],
            "小区名称": row[1],
            "小区坐标": {"x": row[2], "y": row[3]},
            "事件ID": row[4],
            "垃圾房信息": {
                "ID": row[5],
                "名称": row[6],
                "坐标": {"经度": row[7], "纬度": row[8]}
            },
            "区划名称": row[9],
            "落地时间": safe_format_time(row[10]),
            "落地图片": safe_split_images(row[11]),
            "处置信息": {
                "处置时间": safe_format_time(row[12]),
                "是否处置": bool(row[13]),
                "处置图片": safe_split_images(row[14]),
                "是否超时": bool(row[15]),
                "超时图片": safe_split_images(row[16])
            },
            "记录ID": row[17],
            "处置耗时(分钟)": int(row[18]) if row[18] else 0
        }for row in rows]

    def get_inspection_data(self, conditions, params):
        """获取巡查整改数据"""
        sql = self.SQL_INSPECTION_RECTIFICATION.format(
            conditions=" AND ".join(conditions)
        )

        with Database() as db:
            rows = db.execute(sql, params)

        return [{
            "街道名称": row[0],
            "小区名称": row[1],
            "小区坐标": {
                "x": float(row[2]) if row[2] is not None else 0.0,
                "y": float(row[3]) if row[3] is not None else 0.0
            },
            "巡查统计": {
                "巡查次数": int(row[4]),
                "发现问题数": int(row[5]),
                "已整改数": int(row[6]),
                "待整改数": int(row[7])
            },
            "整改率": f"{(row[6] / row[5] * 100):.1f}%" if row[5] > 0 else "0%"
        } for row in rows]


def safe_format_time(time_value):
    """安全处理时间字段"""
    if time_value is None:
        return None

    # 明确使用datetime类型
    if isinstance(time_value, datetime):
        return time_value.strftime('%Y-%m-%d %H:%M:%S')
    elif isinstance(time_value, str):
        return time_value
    return str(time_value)

def safe_split_images(image_data):
    """安全处理图片URL字段（字符串、None、布尔值）"""
    if image_data is None:
        return []
    if isinstance(image_data, bool):  # 处理布尔值
        return []
    if isinstance(image_data, str):
        return image_data.split(',') if image_data else []
    return []









